from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from sqlalchemy.orm import sessionmaker
from datetime import datetime


DBModel = declarative_base()


class Card(DBModel):
    __tablename__ = 'card'  # 数据库表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    card_user = Column(String(30))
    card_uid = Column(String(30))
    # uselist=False  设置一对一关系   backref 反向查询时候对象名称
    card_detail = relationship('CardDetail', uselist=False, backref='card', cascade="all,delete")

    create_time = Column(DateTime, default=datetime.now)
    # onupdate  更新数据的时候，执行
    update_time = Column(DateTime, onupdate=datetime.now, default=datetime.now)

    def __repr__(self):
        return f'<Card Obj id={self.id} card_user={self.card_user}>'


class CardDetail(DBModel):
    __tablename__ = 'card_detail'  # 数据库表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    mail = Column(String(30))
    city = Column(String(10))
    address = Column(String(30))
    card_id = Column(Integer, ForeignKey('card.id'), unique=True)

    def __repr__(self):
        return f'<CardDetail Obj id={self.id} mail={self.mail}>'


if __name__ == '__main__':
    # 为 SQLAlchemy 定义数据库 URL地址
    SQLALCHEMY_DATABASE_URL = 'mysql+pymysql://root:xx123456@39.98.46.92:3309/new_fast'
    engine = create_engine(SQLALCHEMY_DATABASE_URL)  # 配置
    conn = engine.connect()  # 连上
    # 建表
    DBModel.metadata.create_all(engine)  # 将模型映射到数据库中

    Session = sessionmaker(bind=engine)
    # 实例化
    session = Session()

    # 新增数据
    card_obj = Card(card_user='yoyo1', card_uid='aa123333')
    session.add(card_obj)
    session.commit()

    # 新增关联表数据

    # detail_obj = CardDetail(mail='3324@qq.com', city='上海市', address='徐汇区', card_id=1)
    # session.add(detail_obj)
    # session.commit()

    # 查询已有的数据，新增关联表
    # result = session.query(Card).filter_by(card_user='yoyo1').first()
    # detail_obj = CardDetail(mail='3324@qq.com', city='上海市', address='徐汇区', card_id=result.id)
    # session.add(detail_obj)
    # session.commit()



    # 查询数据
    # result = session.query(Card).filter_by(card_user='yoyo1').first()
    # print(result)   # obj
    # print(result.id, result.card_user, result.card_uid)
    #
    # # 查询关联表的数据-----------正向查询
    # print(result.card_detail)
    # print(result.card_detail.id, result.card_detail.mail)

    # 反向查询
    # res = session.query(CardDetail).filter_by(mail='3345@qq.com').first()
    # print(res)
    # print(res.mail, res.address)
    #
    # # 关联表查询主表    反向查询
    # print(res.card)
    # print(res.card.id, res.card.card_user)

    # 删除    cascade="all,delete"  关联表的数据一起删除
    result = session.query(Card).filter_by(card_user='yoyo').first()
    session.delete(result)
    session.commit()


    # 更新主表
    # result = session.query(Card).filter_by(card_user='yoyo1')
    # # update 传dict
    # result.update({"card_uid": '100879'})
    # session.commit()








